PostgreSQL で deadlock を調べる

pq: deadlock detected

code:sql

SELECT

blocked_locks.locktype AS blocked_locktype,

blocked_locks.relation::regclass AS blocked_relation,

blocked_locks.mode AS blocked_mode,

blocked_activity.query AS blocked_query,

blocking_locks.locktype AS blocking_locktype,

blocking_locks.relation::regclass AS blocking_relation,

blocking_locks.mode AS blocking_mode,

blocking_activity.query AS blocking_query

FROM

pg_locks blocked_locks

JOIN pg_stat_activity blocked_activity ON blocked_locks.pid = blocked_activity.pid

JOIN pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype

AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database

AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation

AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page

AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple

AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid

AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid

AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid

AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid

AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid

AND blocking_locks.pid != blocked_locks.pid

JOIN pg_stat_activity blocking_activity ON blocking_locks.pid = blocking_activity.pid

WHERE

NOT blocked_locks.granted;

The following query may be helpful to see what processes are blocking SQL statements (these only find row-level locks, not object-level locks).

code:sql

SELECT

blocked_locks.pid AS blocked_pid,

blocked_activity.usename AS blocked_user,

blocking_locks.pid AS blocking_pid,

blocking_activity.usename AS blocking_user,

blocked_activity.query AS blocked_statement,

blocking_activity.query AS current_statement_in_blocking_process

FROM

pg_catalog.pg_locks blocked_locks

JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid

JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype

AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database

AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation

AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page

AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple

AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid

AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid

AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid

AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid

AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid

AND blocking_locks.pid != blocked_locks.pid

JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid

WHERE

NOT blocked_locks.granted;

待たされてゐる query 一覽

code:sql

SELECT

l.granted,

d.datname,

l.locktype,

relation::regclass,

l.mode,

sa.query_start,

sa.query

FROM

pg_locks l

LEFT JOIN pg_database d ON l.database = d.oid

LEFT JOIN pg_stat_activity sa ON l.pid = sa.pid

WHERE

l.pid != pg_backend_pid()

ORDER BY

l.pid;

pg_locks

pg_database

pg_stat_activity